
df=sqlContext.read.orc("/user/hive/warehouse/dw.db/sms_log")
df.registerTempTable("base")
df=sqlContext.sql("""create table games.tmp_20180721_1 as select * from base where
                 msg_content like '%星辉游戏%'
                 or msg_content like '%天拓%'
                 or msg_content like '%卓越游戏%'
                 or msg_content like '%问道%'
                 or msg_content like '%神武%'
                 or msg_content like '%苍之纪元%'
                 or msg_content like '%游族%'
                 or msg_content like '%雷霆游戏%'
                 """)

df=sqlContext.read.orc("/user/hive/warehouse/dw.db/sms_log")
df.registerTempTable("base")
aaa=sqlContext.sql("""create table games.tmp_20180803 as select * from base where
                 msg_content like '%神之路%'
                 """)


df=sqlContext.read.orc("/user/hive/warehouse/dw.db/sms_log")
df.registerTempTable("base")
aaa=sqlContext.sql("""create table games.tmp_20180805 as select * from base where
                 msg_content like '%本地人自己的传奇%'
                 or msg_content like '%老虎游戏%'
                 """)

df=sqlContext.sql("SELECT t.ttt,count(1) FROM (select FROM_UNIXTIME(UNIX_TIMESTAMP(insert_time),'yyyyMMdd') ttt from games.tmp_20180721_1 where msg_content like '%卓越游戏%') t GROUP BY t.ttt ")




df=sqlContext.read.orc("/user/hive/warehouse/dw.db/sms_log")
df.registerTempTable("base")
aaa=sqlContext.sql("""create table games.liantong_20180808 as select * from base where provider_id =2 and
                 (msg_content like '%游族%'
                 or msg_content like '%星辉游戏%'
                 or msg_content like '%雷霆游戏%'
                 or msg_content like '%问道%')
                 """)


aaa=sqlContext.sql("""
SELECT
    count(case when msg_content like '%游族%' then msisdn end) z_1,
    count(case when msg_content like '%星辉游戏%' then msisdn end) z_2,
    count(case when msg_content like '%雷霆游戏%' then msisdn end) z_3,
    count(case when msg_content like '%问道%' then msisdn end) z_4
from
    games.liantong_20180808 """)
+-----+------+------+------+
|  z_1|   z_2|   z_3|   z_4|
+-----+------+------+------+
|19086|413733|631606|132812|
+-----+------+------+------+

df=sqlContext.read.orc("/user/hive/warehouse/dw.db/sms_log")
df.registerTempTable("base")
aaa=sqlContext.sql("""create table games.chuliuxiang as select * from base where
                 msg_content like '%楚留香%'
                 """)

df=sqlContext.read.orc("/user/hive/warehouse/dw.db/sms_log")
df.registerTempTable("base")
aaa=sqlContext.sql("""create table games.rexuechuanqi as select * from base where
                 msg_content like '%热血传奇%'
                 """)

英语教育

df=sqlContext.read.orc("/user/hive/warehouse/dw.db/sms_log")
df.registerTempTable("base")
aaa=sqlContext.sql("""create table games.english_edu_20180827 as select * from base where
                 msg_content like '%英语%'
                 """)

aaa=sqlContext.sql("""SELECT DISTINCT msisdn FROM games.english_edu  where
                 msg_content like '%哒哒英语%'
                 """)


aaa=sqlContext.sql("""SELECT DISTINCT msisdn FROM games.english_edu  where
                 msg_content like '%VIPKID%'
                 """)


df=sqlContext.sql("create TABLE games.english_edu AS select * FROM games.english_edu WHERE msg_content LIKE '%VIPKID%' AND  prov_id IN (3,18,22) ORDER BY insert_time DESC ")

df=sqlContext.sql("select DISTINCT  msisdn   FROM games.english_edu_1 limit 20000")
writer = pd.ExcelWriter("/home/result/20180822/yingyu.xlsx",engine='xlsxwriter')
df.toPandas().to_excel(writer, sheet_name='Sheet1')
writer.save()